"""
Case Type   : ustore-Consistency
Case Name   : 事务中insert-commit与delete-commit并发,重启集群
Create At   : 2022/09/30
Owner       : cr13
Description :
    1.创建表1并插入数据
    2.创建表2,为表2创建索引
    3.线程1开启事务,设置事务隔离级别并向表2插入数据,提交事务
    4.线程2开启事务,设置事务隔离级别,同时删除表2数据,提交事务
    5.修改表1数据
    6.创建表3并插入数据
    7.重启数据库
    8.修改表1数据
    9.联合查询表2,表3数据
    10.清理环境
Expect      :
    1.成功
    2.成功
    3.开启事务成功,设置事务隔离级别成功,插入表1数据成功,提交事务成功
    4.开启事务成功,设置事务隔离级别成功,删除表2数据成功,提交事务成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.返回查询到的数据
    10.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class CONSISTENCY(unittest.TestCase):
    def setUp(self):
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.table1 = 's_ustore_consistency_02.t_ustore_consistency_0678_01'
        self.table2 = 's_ustore_consistency_02.t_ustore_consistency_0678_02'
        self.index = 's_ustore_consistency_02.i_ustore_consistency_0678'
        self.table3 = 's_ustore_consistency_02.t_ustore_consistency_0678_03'
        self.table4 = 's_ustore_consistency_02.t_ustore_consistency_0001_03'

    def test_ustore_consistency(self):
        text1 = 'step1:创建表1并插入数据 expect:成功'
        self.log.info(text1)
        create_table1 = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table1};
            create table {self.table1}(c_int int) with (storage_type=ustore);
            insert into {self.table1} values(0);''')
        self.log.info(create_table1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table1,
                      '执行失败' + text1)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table1,
                      '执行失败' + text1)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table1,
                      '执行失败' + text1)

        text2 = 'step2:创建表2,为表2创建索引 expect:成功'
        self.log.info(text2)
        create_table2 = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table2};
            create table {self.table2}(c_id int,
            c_d_id int not null,c_w_id int not null,c_first varchar(16) not 
            null,c_middle char(2),c_last varchar(16) not null,
            c_street_1 varchar(20) not null,c_street_2 varchar(20),
            c_city varchar(20) not null,c_state char(2) not null,
            c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(2) not null,c_credit_lim number,
            c_discount number,c_balance number,c_ytd_payment number not null,
            c_payment_cnt int not null,c_delivery_cnt int not null,
            c_data clob not null) with (storage_type=ustore);
            create index {self.index} on {self.table2}(c_id,c_first,c_city);
            ''')
        self.log.info(create_table2)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table2,
                      '执行失败' + text2)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table2,
                      '执行失败' + text2)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG,
                      create_table2, '执行失败' + text2)

        text3 = 'step3:线程1开启事务,设置事务隔离级别并向表2插入数据,提交事务'  \
                'expect:开启事务成功,设置事务隔离级别成功,向表2插入数据成功,' \
                '提交事务成功'
        self.log.info(text3)
        start_trans1 = f'''start transaction;
            set transaction isolation level read committed;
            insert into {self.table2} select c_int,c_int,c_int,'aaaaa','ab',
            'dafkldafkadbfeod','dafkldafkadbfeodefgh','dafkldafkadbfeodefgh',
            'dafkldafkadbfeodefgh','xy','acgfdagfs','dafkldafkadbfeod',null,
            'ad',50000.0,0.4491328,-10.0,10.0,1,0,lpad('qraogfdamjkfdlhgmdjf',
            4000,'qraogfdamjkfdlhgmdjf') from {self.table4};
            select pg_sleep(10);
            commit;'''
        session1 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans1,))
        session1.setDaemon(True)

        text4 = 'step4:线程2开启事务,设置事务隔离级别,同时删除表2数据,提交事务' \
                'expect:开启事务成功,设置事务隔离级别成功,删除表2数据成功,提交' \
                '事务成功'
        self.log.info(text4)
        start_trans2 = f'''start transaction;
            set transaction isolation level read committed;
            delete from {self.table2};
            commit;'''
        session2 = ComThread(self.pri_sh.execut_db_sql, args=(start_trans2,))
        session2.setDaemon(True)
        session1.start()
        session2.start()
        session1.join(15)
        session2.join(5)
        result1 = session1.get_result()
        self.log.info(result1)
        result2 = session2.get_result()
        self.log.info(result2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG,
                      result1, '开启事务失败' + text3)
        self.assertIn(self.constant.SET_SUCCESS_MSG, result1, '执行失败' + text3)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result1,
                      '向表2插入数据失败' + text3)
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, result1, '执行失败' + text3)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG,
                      result2, '开启事务失败' + text4)
        self.assertIn(self.constant.SET_SUCCESS_MSG, result2, '执行失败' + text4)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, result2,
                      '执行失败' + text4)
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, result2, '执行失败' + text4)

        text5 = 'step5:修改表1数据 expect:成功'
        self.log.info(text5)
        update_data = self.pri_sh.execut_db_sql(
            f'''update {self.table1} set c_int=3;
            update {self.table1} set c_int=4;''')
        self.log.info(update_data)
        self.assertEqual(update_data.count(self.constant.UPDATE_SUCCESS_MSG), 
                         2, '执行失败' + text5)

        text6 = 'step6:创建表3并插入数据 expect:成功'
        self.log.info(text6)
        create_table3 = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.table3};
            create table {self.table3}(c_id int,c_d_id int,c_w_id int,c_first 
            varchar(200),c_middle char(2),c_last varchar(16),c_street_1 
            varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state 
            char(2),c_zip char(9),c_phone char(16),c_since timestamp,
            c_credit char(2),c_credit_lim number,c_discount number,c_balance 
            number,c_ytd_payment number,c_payment_cnt int,c_delivery_cnt int,
            c_data clob);
            insert into {self.table3} select * from {self.table2};''')
        self.log.info(create_table3)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, create_table3,
                      '执行失败' + text4)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_table3,
                      '执行失败' + text4)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_table3,
                      '执行失败' + text4)

        text7 = 'step7:重启数据库 expect:成功'
        self.log.info(text7)
        msg = self.pri_sh.restart_db_cluster()
        self.log.info(msg)
        self.assertTrue(msg, '执行失败' + text7)

        text8 = 'step8:修改表1数据 expect:成功'
        self.log.info(text8)
        update_data = self.pri_sh.execut_db_sql(
            f'''update {self.table1} set c_int=5;''')
        self.log.info(update_data)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, update_data,
                      '执行失败' + text8)

        text9 = 'step9:联合查询表2,表3数据 expect:返回查询到的数据'
        self.log.info(text9)
        select_data = self.pri_sh.execut_db_sql(f'''
            select c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt from {self.table2} minus select c_id,c_d_id,c_w_id,
            c_first,c_middle,c_last,c_street_1,c_street_2,c_city,c_state,c_zip,
            c_phone,c_since,c_credit,c_credit_lim,c_discount,c_balance,
            c_ytd_payment,c_payment_cnt,c_delivery_cnt from {self.table3};
            select c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,
            c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,
            c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,
            c_delivery_cnt from {self.table3} minus select c_id,c_d_id,c_w_id,
            c_first,c_middle,c_last,c_street_1,c_street_2,c_city,c_state,
            c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,c_balance,
            c_ytd_payment,c_payment_cnt,c_delivery_cnt from {self.table2};''')
        self.log.info(select_data)
        self.assertEqual(select_data.count('(0 rows)'), 2, '执行失败' + text9)

    def tearDown(self):
        text10 = 'step10:清理环境 expect:成功'
        self.log.info(text10)
        clean_environment = self.pri_sh.execut_db_sql(
            f'''drop table {self.table1} cascade;
            drop table {self.table2} cascade;
            drop table {self.table3} cascade;''')
        self.log.info(clean_environment)
        self.assertEqual(clean_environment.count
                         (self.constant.TABLE_DROP_SUCCESS), 3,
                         '执行失败' + text10)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
